using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;
using System.Collections;

namespace OgilvyOne.SqlServerDAL
{
    public class StaffInfo : IStaffInfo
    {
        // Fields
        private const string PARM_ROLE_UID = "@Member_UID";
        private string sql = "select Staff_ID,Staff_Code,Staff_Name,Nick_Name,Favor,Words,Email,QQ,Sex,Msn,Province,Mobile,City,Status,Firstname,Telephone,Address,Postcode,agegroup,serviceyears from Staff_Info where Staff_ID=";
        private const string SQl_City = "@city";
        private const string SQL_DELETE_ROLE = "delete  Staff_Info where Staff_ID=@Member_UID";
        private const string SQL_Email = "@email";
        private const string SQL_Favor = "@favor";
        private const string SQL_INSERT_STAFF = "INSERT INTO staff_info(Staff_Code,Staff_Name,Password,Area,city,Mobile,Staff_Group,Province,Create_Date) VALUES(@Staff_Code,@Staff_Name,@Password,@Area,@city,@Mobile,@Staff_Group,@Province,getDate())";
        private const string SQL_Mobile = "@mobile";
        private const string SQL_Msn = "@Msn";
        private const string SQL_Province = "@province";
        private const string SQL_QQ = "@qq";
        private string SQL_SELECT_Member_LIST = "select Staff_ID,Staff_Code,Staff_Name from Staff_Info";
        private string SQL_SELECT_Member_Text = "select Staff_ID,Staff_Code,Staff_Name from Staff_Info where Staff_Code='";
        private const string SQL_Sex = "@sex";
        private const string SQL_STAFF_CHECK = " select staff_Id, staff_Name,staff_Code, password, isnull(nick_Name,''),isnull(staff_Header,''),isnull(area,1),mobile,isnull(province,''),isnull(city,''),isnull(staff_Group,''),isnull(favor,''),isnull(email,''),isnull(qq,''),isnull(msn,''),create_Date,isnull(last_Login_Date,''),isnull(last_Login_Ip,''),login_Times,isnull(words,''),scores,last_Stauts_Date,sex,isnull(Firstname,''),isnull(Telephone,''),isnull(Address,''),isnull(Postcode,''),isnull(Agegroup,''),isnull(Serviceyears,0), isnull(skin, 1) from staff_info where staff_code=@Staff_Code and password = @Password and STATUS=0";
        private const string SQL_STAFF_INFO = " select staff_Id, staff_Name,staff_Code, password, isnull(nick_Name,''),isnull(staff_Header,''),isnull(area,1),mobile,isnull(province,''),isnull(city,''),isnull(staff_Group,''),isnull(favor,''),isnull(email,''),isnull(qq,''),isnull(msn,''),create_Date,isnull(last_Login_Date,''),isnull(last_Login_Ip,''),login_Times,isnull(words,''),scores,last_Stauts_Date,sex,isnull(Firstname,''),isnull(Telephone,''),isnull(Address,''),isnull(Postcode,''),isnull(Agegroup,''),isnull(Serviceyears,0), isnull(skin, 1) from staff_info where staff_id=@Staff_ID";
        private const string SQL_Status = "@status";
        private const string SQL_UPDATE_STAFF = "update staff_info set Staff_Name=@Staff_Name,Area=@Area,city=@city,Mobile=@Mobile,Staff_Group=@Staff_Group,Province=@Province where Staff_Code=@Staff_Code";
        private const string SQL_UPDATE_STAFF_INFO = "update Staff_Info set Favor=@Favor,Words=@Words,Email=@Email,QQ=@Qq,Sex=@Sex,Firstname=@Firstname,Telephone=@Telephone,address=@Address ,postcode=@Postcode, agegroup=@Agegroup, serviceyears=@Serviceyears, Nick_name=@nick_name, Mobile=@Mobile, Msn=@msn,staff_group=@Staff_Group,city=@City,area=@Area,province=@Province,staff_header=@Staff_Header where Staff_ID=@staffId";
        private const string SQL_UPDATE_STAFF_PASSWD = "update Staff_Info set Password=@Password,Email=@Email where staff_id=@Staff_Id and Password=@OldPassword";
        private const string SQL_UPDATE_USER = "update Staff_Info set Favor=@Favor,Words=@Words,Email=@Email,QQ=@qq,Sex=@sex,Firstname=@firstname,Telephone=@telephone,address=@adress ,postcode=@postcode, agegroup=@agegroup, serviceyears=@serviceyears, Nick_name=@nick_name, Mobile=@Mobile, Msn=@msn,Status=@member_status where Staff_ID=@staffId and STATUS=0";
        private const string SQL_Words = "@words";

        // Methods
        public bool addScoreStaff(string strStaffId, string ruleId, string score)
        {
            IList list = new ArrayList();
            bool flag = false;
            string cmdText = " update staff_info set scores=scores+" + score + " where staff_id in(" + strStaffId + ")";
            string str2 = " insert into SCORE_LOG(STAFF_ID,SCORE_RULE_ID,SCORE_VALUE,CREATE_DATE,USER_ID) select staff_id," + ruleId + "," + score + ",getdate(),0 from staff_info where staff_id in(" + strStaffId + ")";
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, null);
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, str2, null);
                        transaction.Commit();
                        flag = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    return flag;
                }
            }
        }

        public void Delete(int Member_UID)
        {
            SqlParameter[] roleDeleteParameters = GetRoleDeleteParameters();
            roleDeleteParameters[0].Value = Member_UID;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "delete  Staff_Info where Staff_ID=@Member_UID", roleDeleteParameters);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList getMember_data(int Member_UID)
        {
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, this.sql + Member_UID, null))
            {
                while (reader.Read())
                {
                    string str = string.Empty;
                    string str2 = string.Empty;
                    string str3 = string.Empty;
                    string favor = string.Empty;
                    string words = string.Empty;
                    string email = string.Empty;
                    string qQ = string.Empty;
                    string sex = string.Empty;
                    string msn = string.Empty;
                    string province = string.Empty;
                    string mobile = string.Empty;
                    string city = string.Empty;
                    int status = 0;
                    string sfirstname = string.Empty;
                    string stelephone = string.Empty;
                    string saddress = string.Empty;
                    string spostcode = string.Empty;
                    string sagegroup = string.Empty;
                    int serviceyears = 0;
                    if (reader.IsDBNull(1))
                    {
                        str = "";
                    }
                    else
                    {
                        str = reader.GetString(1);
                    }
                    if (reader.IsDBNull(2))
                    {
                        str2 = "";
                    }
                    else
                    {
                        str2 = reader.GetString(2);
                    }
                    if (reader.IsDBNull(3))
                    {
                        str3 = "";
                    }
                    else
                    {
                        str3 = reader.GetString(3);
                    }
                    if (reader.IsDBNull(4))
                    {
                        favor = "";
                    }
                    else
                    {
                        favor = reader.GetString(4);
                    }
                    if (reader.IsDBNull(5))
                    {
                        words = "";
                    }
                    else
                    {
                        words = reader.GetString(5);
                    }
                    if (reader.IsDBNull(6))
                    {
                        email = "";
                    }
                    else
                    {
                        email = reader.GetString(6);
                    }
                    if (reader.IsDBNull(7))
                    {
                        qQ = "";
                    }
                    else
                    {
                        qQ = reader.GetString(7);
                    }
                    if (reader.IsDBNull(8))
                    {
                        sex = "";
                    }
                    else
                    {
                        sex = reader.GetString(8);
                    }
                    if (reader.IsDBNull(9))
                    {
                        msn = "";
                    }
                    else
                    {
                        msn = reader.GetString(9);
                    }
                    if (reader.IsDBNull(10))
                    {
                        province = "";
                    }
                    else
                    {
                        province = reader.GetString(10);
                    }
                    if (reader.IsDBNull(11))
                    {
                        mobile = "";
                    }
                    else
                    {
                        mobile = reader.GetString(11);
                    }
                    if (reader.IsDBNull(12))
                    {
                        city = "";
                    }
                    else
                    {
                        city = reader.GetString(12);
                    }
                    if (reader.IsDBNull(13))
                    {
                        status = 0;
                    }
                    else
                    {
                        status = reader.GetByte(13);
                    }
                    if (reader.IsDBNull(14))
                    {
                        sfirstname = "";
                    }
                    else
                    {
                        sfirstname = reader.GetString(14);
                    }
                    if (reader.IsDBNull(15))
                    {
                        stelephone = "";
                    }
                    else
                    {
                        stelephone = reader.GetString(15);
                    }
                    if (reader.IsDBNull(0x10))
                    {
                        saddress = "";
                    }
                    else
                    {
                        saddress = reader.GetString(0x10);
                    }
                    if (reader.IsDBNull(0x11))
                    {
                        spostcode = "";
                    }
                    else
                    {
                        spostcode = reader.GetString(0x11);
                    }
                    if (reader.IsDBNull(0x12))
                    {
                        sagegroup = "";
                    }
                    else
                    {
                        sagegroup = reader.GetString(0x12);
                    }
                    if (reader.IsDBNull(0x13))
                    {
                        serviceyears = 0;
                    }
                    else
                    {
                        serviceyears = reader.GetInt32(0x13);
                    }
                    StaffInfoInfo info = new StaffInfoInfo(reader.GetInt32(0), str, str2, str3, favor, words, email, qQ, sex, msn, province, mobile, city, status, sfirstname, stelephone, saddress, spostcode, sagegroup, serviceyears);
                    list.Add(info);
                }
            }
            return list;
        }

        private static SqlParameter[] GetRoleDeleteParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("delete  Staff_Info where Staff_ID=@Member_UID");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Member_UID", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("delete  Staff_Info where Staff_ID=@Member_UID", cachedParameters);
            }
            return cachedParameters;
        }

        public static string GetScoreImage(int score)
        {
            return GetScoreImage(score, false);
        }

        public static string GetScoreImage(int score, bool isBig)
        {
            return "/images/icon_diamond.gif";
        }

        public StaffInfoInfo getStaffInfo(int staffId)
        {
            StaffInfoInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Staff_Id", SqlDbType.Int, 4, "Staff_Id") };
            cmdParms[0].Value = staffId;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, " select staff_Id, staff_Name,staff_Code, password, isnull(nick_Name,''),isnull(staff_Header,''),isnull(area,1),mobile,isnull(province,''),isnull(city,''),isnull(staff_Group,''),isnull(favor,''),isnull(email,''),isnull(qq,''),isnull(msn,''),create_Date,isnull(last_Login_Date,''),isnull(last_Login_Ip,''),login_Times,isnull(words,''),scores,last_Stauts_Date,sex,isnull(Firstname,''),isnull(Telephone,''),isnull(Address,''),isnull(Postcode,''),isnull(Agegroup,''),isnull(Serviceyears,0), isnull(skin, 1) from staff_info where staff_id=@Staff_ID", cmdParms))
            {
                while (reader.Read())
                {
                    info = new StaffInfoInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetInt32(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetDateTime(15), reader.GetDateTime(0x10), reader.GetString(0x11), reader.GetInt32(0x12), reader.GetString(0x13), reader.GetInt32(20), reader.GetDateTime(0x15), reader.GetString(0x16), reader.GetString(0x17), reader.GetString(0x18), reader.GetString(0x19), reader.GetString(0x1a), reader.GetString(0x1b), reader.GetInt32(0x1c));
                    info.Skin = reader.GetByte(0x1d);
                }
            }
            return info;
        }

        public StaffInfoInfo getStaffInfo(string staffCode, string password)
        {
            StaffInfoInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Staff_Code", SqlDbType.VarChar, 0x26, "Staff_Code"), new SqlParameter("@Password", SqlDbType.VarChar, 20, "Password") };
            cmdParms[0].Value = staffCode;
            cmdParms[1].Value = password;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, " select staff_Id, staff_Name,staff_Code, password, isnull(nick_Name,''),isnull(staff_Header,''),isnull(area,1),mobile,isnull(province,''),isnull(city,''),isnull(staff_Group,''),isnull(favor,''),isnull(email,''),isnull(qq,''),isnull(msn,''),create_Date,isnull(last_Login_Date,''),isnull(last_Login_Ip,''),login_Times,isnull(words,''),scores,last_Stauts_Date,sex,isnull(Firstname,''),isnull(Telephone,''),isnull(Address,''),isnull(Postcode,''),isnull(Agegroup,''),isnull(Serviceyears,0), isnull(skin, 1) from staff_info where staff_code=@Staff_Code and password = @Password and STATUS=0", cmdParms))
            {
                while (reader.Read())
                {
                    info = new StaffInfoInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetInt32(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetDateTime(15), reader.GetDateTime(0x10), reader.GetString(0x11), reader.GetInt32(0x12), reader.GetString(0x13), reader.GetInt32(20), reader.GetDateTime(0x15), reader.GetString(0x16), reader.GetString(0x17), reader.GetString(0x18), reader.GetString(0x19), reader.GetString(0x1a), reader.GetString(0x1b), reader.GetInt32(0x1c));
                    info.Skin = reader.GetByte(0x1d);
                }
            }
            return info;
        }

        public int getStaffInfoCount(string staffCode)
        {
            int num = 0;
            string cmdText = " select count(*) from staff_info where staff_code =@Staff_Code";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Staff_Code", SqlDbType.VarChar, 0x26, "Staff_Code") };
            cmdParms[0].Value = staffCode;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }
            }
            return num;
        }

        public IList getStaffList(StaffInfoInfo info)
        {
            IList list = new ArrayList();
            string cmdText = " select staff_id,area,staff_code,mobile,province,city,staff_group,staff_name,nick_name from staff_info where 1=1";
            if (info.Area != 0)
            {
                object obj2 = cmdText;
                cmdText = string.Concat(new object[] { obj2, " and area='", info.Area, "'" });
            }
            if ((info.City != null) && (info.City.Length > 0))
            {
                cmdText = cmdText + " and City='" + info.City + "'";
            }
            if ((info.Province != null) && (info.Province.Length > 0))
            {
                cmdText = cmdText + " and Province='" + info.Province + "'";
            }
            if ((info.StaffCode != null) && (info.StaffCode.Length > 0))
            {
                cmdText = cmdText + " and Staff_Code like '%" + info.StaffCode + "%'";
            }
            if ((info.StaffGroup != null) && (info.StaffGroup.Length > 0))
            {
                cmdText = cmdText + " and Staff_Group='" + info.StaffGroup + "'";
            }
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    int area = 0;
                    string staffCode = string.Empty;
                    string mobile = string.Empty;
                    string province = string.Empty;
                    string city = string.Empty;
                    string str6 = string.Empty;
                    string staffGroup = string.Empty;
                    string staffName = string.Empty;
                    if (reader.IsDBNull(1))
                    {
                        area = 0;
                    }
                    else
                    {
                        area = reader.GetInt32(1);
                    }
                    if (reader.IsDBNull(2))
                    {
                        staffCode = "";
                    }
                    else
                    {
                        staffCode = reader.GetString(2);
                    }
                    if (reader.IsDBNull(3))
                    {
                        mobile = "";
                    }
                    else
                    {
                        mobile = reader.GetString(3);
                    }
                    if (reader.IsDBNull(4))
                    {
                        province = "";
                    }
                    else
                    {
                        province = reader.GetString(4);
                    }
                    if (reader.IsDBNull(5))
                    {
                        city = "";
                    }
                    else
                    {
                        city = reader.GetString(5);
                    }
                    if (reader.IsDBNull(6))
                    {
                        staffGroup = "";
                    }
                    else
                    {
                        staffGroup = reader.GetString(6);
                    }
                    if (reader.IsDBNull(7))
                    {
                        staffName = "";
                    }
                    else
                    {
                        staffName = reader.GetString(7);
                    }
                    if (reader.IsDBNull(8))
                    {
                        str6 = "";
                    }
                    else
                    {
                        str6 = reader.GetString(8);
                    }
                    StaffInfoInfo info2 = new StaffInfoInfo(reader.GetInt32(0), area, staffCode, mobile, province, city, staffGroup, staffName, str6);
                    list.Add(info2);
                }
            }
            return list;
        }

        public int getStaffRank(int staffId)
        {
            int num = 0;
            string cmdText = " select rank from staff_rank where staff_id =" + staffId;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }
            }
            return num;
        }

        public StaffInfoInfo getStaffSession()
        {
            StaffInfoInfo info = null;
            if (HttpContext.Current.Session["STAFF_KEY"] == null)
            {
                if (HttpContext.Current.Request.Cookies["wowCookie"] == null)
                {
                    HttpContext.Current.Response.Redirect("/login.aspx");
                    return info;
                }
                HttpCookie cookie = HttpContext.Current.Request.Cookies["wowCookie"];
                string s = Cryptography.Decrypt(cookie.Value.ToString()).Split(new char[] { ',' })[0];
                info = new StaffInfo().getStaffInfo(int.Parse(s));
                HttpContext.Current.Session["STAFF_KEY"] = info;
                return info;
            }
            return (StaffInfoInfo)HttpContext.Current.Session["STAFF_KEY"];
        }

        public void Insert(StaffInfoInfo acc)
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("INSERT INTO staff_info(Staff_Code,Staff_Name,Password,Area,city,Mobile,Staff_Group,Province,Create_Date) VALUES(@Staff_Code,@Staff_Name,@Password,@Area,@city,@Mobile,@Staff_Group,@Province,getDate())");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Staff_Code", SqlDbType.VarChar, 50, "Staff_Code"), new SqlParameter("@Staff_Name", SqlDbType.VarChar, 50, "Staff_Name"), new SqlParameter("@Password", SqlDbType.VarChar, 50, "Password"), new SqlParameter("@Area", SqlDbType.Int, 4, "Area"), new SqlParameter("@city", SqlDbType.VarChar, 20, "city"), new SqlParameter("@Mobile", SqlDbType.VarChar, 20, "Mobile"), new SqlParameter("@Staff_Group", SqlDbType.VarChar, 50, "Staff_Group"), new SqlParameter("@Province", SqlDbType.VarChar, 50, "Staff_Group") };
            }
            cachedParameters[0].Value = acc.StaffCode;
            cachedParameters[1].Value = acc.StaffName;
            cachedParameters[2].Value = acc.Password;
            cachedParameters[3].Value = acc.Area;
            cachedParameters[4].Value = acc.City;
            cachedParameters[5].Value = acc.Mobile;
            cachedParameters[6].Value = acc.StaffGroup;
            cachedParameters[7].Value = acc.Province;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "INSERT INTO staff_info(Staff_Code,Staff_Name,Password,Area,city,Mobile,Staff_Group,Province,Create_Date) VALUES(@Staff_Code,@Staff_Name,@Password,@Area,@city,@Mobile,@Staff_Group,@Province,getDate())", cachedParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void InsertLoginLog(int staffId, string ip)
        {
            string cacheKey = " insert into staff_login_log(staff_id,Login_Date,Login_IP) values(@Staff_Id,getdate(),@Login_IP)";
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters(cacheKey);
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Staff_Id", SqlDbType.Int, 4, "Staff_Id"), new SqlParameter("@Login_IP", SqlDbType.VarChar, 50, "Login_IP") };
            }
            cachedParameters[0].Value = staffId;
            cachedParameters[1].Value = ip;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cacheKey, cachedParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public int isFirstLogin(string staffCode)
        {
            int num = 0;
            string cmdText = " select login_times from staff_info where staff_code =@Staff_Code";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Staff_Code", SqlDbType.VarChar, 0x26, "Staff_Code") };
            cmdParms[0].Value = staffCode;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }
            }
            return num;
        }

        public void Update(StaffInfoInfo acc)
        {
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Password", SqlDbType.VarChar, 50, "Password") };
            cmdParms[0].Value = acc.Password;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "update staff_info set Staff_Name=@Staff_Name,Area=@Area,city=@city,Mobile=@Mobile,Staff_Group=@Staff_Group,Province=@Province where Staff_Code=@Staff_Code", cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Update_Member(StaffInfoInfo acc)
        {
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@staffId", SqlDbType.Int, 4), new SqlParameter("@Favor", SqlDbType.VarChar, 0x7d0), new SqlParameter("@Words", SqlDbType.VarChar, 0x7d0), new SqlParameter("@Email", SqlDbType.VarChar, 50), new SqlParameter("@qq", SqlDbType.VarChar, 20), new SqlParameter("@sex", SqlDbType.VarChar, 20), new SqlParameter("@msn", SqlDbType.VarChar, 20), new SqlParameter("@Mobile", SqlDbType.VarChar, 20), new SqlParameter("@member_status", SqlDbType.VarChar, 50), new SqlParameter("@firstname", SqlDbType.VarChar, 50), new SqlParameter("@telephone", SqlDbType.VarChar, 20), new SqlParameter("@adress", SqlDbType.VarChar, 500), new SqlParameter("@postcode", SqlDbType.VarChar, 10), new SqlParameter("@agegroup", SqlDbType.VarChar, 50), new SqlParameter("@serviceyears", SqlDbType.Int, 4), new SqlParameter("@nick_name", SqlDbType.VarChar, 50) };
            cmdParms[0].Value = acc.StaffId;
            cmdParms[1].Value = acc.Favor;
            cmdParms[2].Value = acc.Words;
            cmdParms[3].Value = acc.Email;
            cmdParms[4].Value = acc.Qq;
            cmdParms[5].Value = acc.Sex;
            cmdParms[6].Value = acc.Msn;
            cmdParms[7].Value = acc.Mobile;
            cmdParms[8].Value = acc.Status;
            cmdParms[9].Value = acc.Firstname;
            cmdParms[10].Value = acc.Telephone;
            cmdParms[11].Value = acc.Address;
            cmdParms[12].Value = acc.Postcode;
            cmdParms[13].Value = acc.Agegroup;
            cmdParms[14].Value = acc.Serviceyears;
            cmdParms[15].Value = acc.NickName;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "update Staff_Info set Favor=@Favor,Words=@Words,Email=@Email,QQ=@qq,Sex=@sex,Firstname=@firstname,Telephone=@telephone,address=@adress ,postcode=@postcode, agegroup=@agegroup, serviceyears=@serviceyears, Nick_name=@nick_name, Mobile=@Mobile, Msn=@msn,Status=@member_status where Staff_ID=@staffId and STATUS=0", cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void UpdateLoginLog(int staffId, string ip)
        {
            string cmdText = string.Concat(new object[] { " update  staff_login_log set Quit_Date=getdate(),Quit_IP='", ip, "' where staff_id=", staffId, " and Login_Date=(select max(Login_Date) from staff_login_log where staff_id=", staffId, ")" });
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, null);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void UpdateSkin(int staffId, int skin)
        {
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@skin", SqlDbType.Int, 4, "skin"), new SqlParameter("@Staff_Id", SqlDbType.Int, 4, "Staff_Id") };
            cmdParms[0].Value = skin;
            cmdParms[1].Value = staffId;
            string cmdText = " update staff_info set skin=@skin where staff_id=@Staff_Id";
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public int UpdateStaffInfo(StaffInfoInfo staffInfoInfo)
        {
            int num = 0;
            SqlParameter[] cmdParms = new SqlParameter[] { 
            new SqlParameter("@staffId", SqlDbType.Int, 4), new SqlParameter("@Favor", SqlDbType.VarChar, 0x7d0), new SqlParameter("@Words", SqlDbType.VarChar, 0x7d0), new SqlParameter("@Email", SqlDbType.VarChar, 50), new SqlParameter("@qq", SqlDbType.VarChar, 20), new SqlParameter("@sex", SqlDbType.VarChar, 1), new SqlParameter("@msn", SqlDbType.VarChar, 20), new SqlParameter("@Mobile", SqlDbType.VarChar, 20), new SqlParameter("@Firstname", SqlDbType.VarChar, 50), new SqlParameter("@Telephone", SqlDbType.VarChar, 20), new SqlParameter("@Address", SqlDbType.VarChar, 500), new SqlParameter("@Postcode", SqlDbType.VarChar, 10), new SqlParameter("@Agegroup", SqlDbType.VarChar, 50), new SqlParameter("@Serviceyears", SqlDbType.Int, 4), new SqlParameter("@Nick_Name", SqlDbType.VarChar, 50), new SqlParameter("@Staff_Group", SqlDbType.VarChar, 50), 
            new SqlParameter("@City", SqlDbType.VarChar, 50), new SqlParameter("@Area", SqlDbType.Int, 4), new SqlParameter("@Province", SqlDbType.VarChar, 20), new SqlParameter("@Staff_Header", SqlDbType.VarChar, 0xff)
         };
            cmdParms[0].Value = staffInfoInfo.StaffId;
            cmdParms[1].Value = staffInfoInfo.Favor;
            cmdParms[2].Value = staffInfoInfo.Words;
            cmdParms[3].Value = staffInfoInfo.Email;
            cmdParms[4].Value = staffInfoInfo.Qq;
            cmdParms[5].Value = staffInfoInfo.Sex;
            cmdParms[6].Value = staffInfoInfo.Msn;
            cmdParms[7].Value = staffInfoInfo.Mobile;
            cmdParms[8].Value = staffInfoInfo.Firstname;
            cmdParms[9].Value = staffInfoInfo.Telephone;
            cmdParms[10].Value = staffInfoInfo.Address;
            cmdParms[11].Value = staffInfoInfo.Postcode;
            cmdParms[12].Value = staffInfoInfo.Agegroup;
            cmdParms[13].Value = staffInfoInfo.Serviceyears;
            cmdParms[14].Value = staffInfoInfo.NickName;
            cmdParms[15].Value = staffInfoInfo.StaffGroup;
            cmdParms[0x10].Value = staffInfoInfo.City;
            cmdParms[0x11].Value = staffInfoInfo.Area;
            cmdParms[0x12].Value = staffInfoInfo.Province;
            cmdParms[0x13].Value = staffInfoInfo.StaffHeader;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "update Staff_Info set Favor=@Favor,Words=@Words,Email=@Email,QQ=@Qq,Sex=@Sex,Firstname=@Firstname,Telephone=@Telephone,address=@Address ,postcode=@Postcode, agegroup=@Agegroup, serviceyears=@Serviceyears, Nick_name=@nick_name, Mobile=@Mobile, Msn=@msn,staff_group=@Staff_Group,city=@City,area=@Area,province=@Province,staff_header=@Staff_Header where Staff_ID=@staffId", cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }

        public int UpdateStaffPasswd(int staffId, string newPasswd, string oldPasswd, string email)
        {
            int num = 0;
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("update Staff_Info set Password=@Password,Email=@Email where staff_id=@Staff_Id and Password=@OldPassword");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Password", SqlDbType.VarChar, 50, "Password"), new SqlParameter("@Email", SqlDbType.VarChar, 50, "Email"), new SqlParameter("@Staff_Id", SqlDbType.Int, 4, "Staff_Id"), new SqlParameter("@OldPassword", SqlDbType.VarChar, 50, "Password") };
            }
            cachedParameters[0].Value = newPasswd;
            cachedParameters[1].Value = email;
            cachedParameters[2].Value = staffId;
            cachedParameters[3].Value = oldPasswd;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "update Staff_Info set Password=@Password,Email=@Email where staff_id=@Staff_Id and Password=@OldPassword", cachedParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }
    }
}